/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package ortus.db;

import java.sql.Statement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import ortus.SageMediaObject;
import sagex.api.MediaFileAPI;

/**
 *
 * @author jphipps
 */
public class DBAccess extends ortus.ortusvars {

   public DBAccess() {
   }
     /**
     * Execute DDL or update query such as create, insert, update, delete
     * @param sql SQL to perform
     * @return truefalse for success or failure.  Failed calls will log error message to the debuglog
     */
    public int executeSQL(Connection conn, String sql) {
        try {
          Statement stmt = conn.createStatement();
          stmt.execute(sql);
          int UpdateCount = stmt.getUpdateCount();
          stmt.close();
//	  conn.close();
          return UpdateCount;
        } catch(SQLException e ) {
//	    try {  conn.close(); } catch( Exception ex) {}
            ortus.api.DebugLog(ERROR, "SQL: " + sql);
            ortus.api.DebugLog(ERROR, "SQLstate: " + e.getSQLState() );
            ortus.api.DebugLog(ERROR, "SQLException: " + e);
            return -1;
        }
    }
/**
 * Execute a query to return data such as select
 * @param sql SQL to execute
 * @return An array of results
 */
    public List<Object> executeSQLQuery(Connection conn, String sql) {
        List<Object> ra = new ArrayList<Object>();
        int recCount = 0;
        try {
          Statement stmt = conn.createStatement();
          ResultSet rs = stmt.executeQuery(sql);
          while( rs.next()) {
              recCount++;
              String returnrow = "";
              int fCount = 0;
              ResultSetMetaData rsmd = rs.getMetaData();
              for ( int x = 1; x <= rsmd.getColumnCount();x++) {
                 fCount++;
                  if( fCount > 1)
                      returnrow+=",";
                  if ( rs.getString(x) != null)
                      returnrow+=rs.getString(x).trim();
                  else
                      returnrow+="";
              }
              ra.add(returnrow);
          }
          rs.close();
          stmt.close();
//	  conn.close();
          return ra;
        } catch(Exception e ) {
//	    try {  conn.close(); } catch( Exception ex) {}
            ortus.api.DebugLog(ERROR,"SQL: " + sql);
            ortus.api.DebugLog( ERROR, "SQLException: " + e);
            return null;
        }
    }

/**
 * Execute a query to return data such as select
 * @param sql SQL to execute
 * @return An array of results
 */
    public List<Object> executeSQLQueryCache(Connection conn, String sql) {
	Object ci = ortus.cache.cacheEngine.getInstance().getProvider().Get(ortus.api.MD5Sum("Query:" + sql));
	if ( ci != null)
		return (List)ci;

        List<Object> ra = new ArrayList<Object>();
        int recCount = 0;
        try {
          Statement stmt = conn.createStatement();
          ResultSet rs = stmt.executeQuery(sql);
          while( rs.next()) {
              recCount++;
              String returnrow = "";
              int fCount = 0;
              ResultSetMetaData rsmd = rs.getMetaData();
              for ( int x = 1; x <= rsmd.getColumnCount();x++) {
                 fCount++;
                  if( fCount > 1)
                      returnrow+=",";
                  if ( rs.getString(x) != null)
                      returnrow+=rs.getString(x).trim();
                  else
                      returnrow+="";
              }
              ra.add(returnrow);
          }
          rs.close();
          stmt.close();
	  ortus.cache.cacheEngine.getInstance().getProvider().Put(ortus.api.MD5Sum("Query:" + sql), ra);
          return ra;
        } catch(Exception e ) {
//	    try {  conn.close(); } catch( Exception ex) {}
            ortus.api.DebugLog(ERROR,"SQL: " + sql);
            ortus.api.DebugLog( ERROR, "SQLException: " + e);
            return null;
        }
    }
/**
 * Execute a query to return data such as select
 * @param sql SQL to execute
 * @return An array of results
 */
    public List<List> executeSQLQueryArray(Connection conn, String sql) {
        List<List> ra = new ArrayList<List>();
        int recCount = 0;
        try {
          Statement stmt = conn.createStatement();
          ResultSet rs = stmt.executeQuery(sql);
          while( rs.next()) {
              List recarray = new ArrayList();
              recCount++;
              String returnrow = "";
              int fCount = 0;
              ResultSetMetaData rsmd = rs.getMetaData();
              for ( int x = 1; x <= rsmd.getColumnCount();x++) {
                  recarray.add(rs.getString(x));
              }
              ra.add(recarray);
          }
          rs.close();
          stmt.close();
//	  conn.close();
          return ra;
        } catch(Exception e ) {
//	    try {  conn.close(); } catch( Exception ex) {}
            ortus.api.DebugLog(ERROR,"SQL: " + sql);
            ortus.api.DebugLog( ERROR, "SQLException: " + e);
            return null;
        }
    }
/**
 * Execute a query to return data such as select
 * @param sql SQL to execute
 * @return An array of results
 */
    public List<List> executeSQLQueryArrayCache(Connection conn, String sql) {
	Object ci = ortus.cache.cacheEngine.getInstance().getProvider().Get(ortus.api.MD5Sum("Array:" + sql));
	if ( ci != null)
		return (List)ci;

        List<List> ra = new ArrayList<List>();
        int recCount = 0;
        try {
          Statement stmt = conn.createStatement();
          ResultSet rs = stmt.executeQuery(sql);
          while( rs.next()) {
              List recarray = new ArrayList();
              recCount++;
              String returnrow = "";
              int fCount = 0;
              ResultSetMetaData rsmd = rs.getMetaData();
              for ( int x = 1; x <= rsmd.getColumnCount();x++) {
                  recarray.add(rs.getString(x));
              }
              ra.add(recarray);
          }
          rs.close();
          stmt.close();
	  ortus.cache.cacheEngine.getInstance().getProvider().Put(ortus.api.MD5Sum("Array:" + sql), ra);
          return ra;
        } catch(Exception e ) {
//	    try {  conn.close(); } catch( Exception ex) {}
            ortus.api.DebugLog(ERROR,"SQL: " + sql);
            ortus.api.DebugLog( ERROR, "SQLException: " + e);
            return null;
        }
    }

/**
 * Execute a query to return data such as select
 * @param sql SQL to execute
 * @return An array of results
 */
    public List<HashMap> executeSQLQueryHash(Connection conn, String sql) {
        List<HashMap> ra = new ArrayList<HashMap>();
        int recCount = 0;
        try {
          Statement stmt = conn.createStatement();
          ResultSet rs = stmt.executeQuery(sql);
          while( rs.next()) {
              List recarray = new ArrayList();
              recCount++;
              String returnrow = "";
              int fCount = 0;
              ResultSetMetaData rsmd = rs.getMetaData();
	      HashMap rechash = new HashMap();
              for ( int x = 1; x <= rsmd.getColumnCount();x++) {
		      rechash.put(rsmd.getColumnName(x), rs.getString(x));
              }
              ra.add(rechash);
          }
          rs.close();
          stmt.close();
//	  conn.close();
          return ra;
        } catch(Exception e ) {
//	    try {  conn.close(); } catch( Exception ex) {}
            ortus.api.DebugLog(ERROR,"SQL: " + sql);
            ortus.api.DebugLog( ERROR, "SQLException: " + e);
            return null;
        }
    }
/**
 * Execute a query to return data such as select
 * @param sql SQL to execute
 * @return An array of results
 */
    public List<HashMap> executeSQLQueryHashCache(Connection conn, String sql) {
	Object ci = ortus.cache.cacheEngine.getInstance().getProvider().Get(ortus.api.MD5Sum("Hash:" + sql));
	if ( ci != null)
		return (List)ci;

        List<HashMap> ra = new ArrayList<HashMap>();
        int recCount = 0;
        try {
          Statement stmt = conn.createStatement();
          ResultSet rs = stmt.executeQuery(sql);
          while( rs.next()) {
              List recarray = new ArrayList();
              recCount++;
              String returnrow = "";
              int fCount = 0;
              ResultSetMetaData rsmd = rs.getMetaData();
 	      HashMap rechash = new HashMap();
              for ( int x = 1; x <= rsmd.getColumnCount();x++) {
		      rechash.put(rsmd.getColumnName(x), rs.getString(x));
              }
              ra.add(rechash);
          }
          rs.close();
          stmt.close();
	  ortus.cache.cacheEngine.getInstance().getProvider().Put(ortus.api.MD5Sum("Hash:" + sql), ra);
          return ra;
        } catch(Exception e ) {
//	    try {  conn.close(); } catch( Exception ex) {}
            ortus.api.DebugLog(ERROR,"SQL: " + sql);
            ortus.api.DebugLog( ERROR, "SQLException: " + e);
            return null;
        }
    }

    public int executeSQLQueryFile(Connection conn, String sqlfile) {
        List<String> sql = ortus.util.dump.DumpLogFile(sqlfile);
        int totalrows = 0;

        String sqlglob ="";
        for ( String x : sql) {
            x = x.trim();
            if ( ! x.isEmpty())
               sqlglob+= " " + x;
        }

        String[] sqlstmts = sqlglob.split(";");
        for ( String s : sqlstmts) {
        ortus.api.DebugLog(TRACE2, "executeSQLQueryFile: executing: " + s);
            totalrows+=executeSQL(conn, s);
        }
        return totalrows;
    }
     /**
     * Return an array of media objects that matches the passed where clause
     * @param sql where cluase
     * @return Media Object Array
     */
    public List<Object> getMediaFilesSQL(Connection conn, String sql) {
        List<Object> mfl = new ArrayList<Object>();
        Statement stmt;
        try {
            stmt = conn.createStatement();
            ResultSet rs = null;

            rs = stmt.executeQuery(sql);

            while ( rs.next() ) {
		    if ( rs.getInt("mediaid") > 0)
//			    mfl.add(new SageMediaObject(MediaFileAPI.GetMediaFileForID(rs.getInt("mediaid"))));
			mfl.add(MediaFileAPI.GetMediaFileForID(rs.getInt("mediaid")));

            }

            rs.close();
            stmt.close();
//	    conn.close();
            return mfl;

        } catch (SQLException ex) {
//	    try {  conn.close(); } catch( Exception e) {}
            ortus.api.DebugLog(ERROR,"SQLException: " + ex);
        }
        return null;
    }
     /**
     * Return an array of media objects that matches the passed where clause
     * @param sql where cluase
     * @return Media Object Array
     */
    public List<Object> getSageMediaFilesSQL(Connection conn, String sql) {
        List<Object> mfl = new ArrayList<Object>();
        Statement stmt;
        try {
            stmt = conn.createStatement();
            ResultSet rs = null;

            rs = stmt.executeQuery(sql);

            while ( rs.next() ) {
		    if ( rs.getInt("mediaid") > 0)
//			    mfl.add(new SageMediaObject(MediaFileAPI.GetMediaFileForID(rs.getInt("mediaid"))));
			mfl.add(ortus.cache.cacheEngine.getInstance().GetSageMediaCache(rs.getInt("mediaid")));

            }

            rs.close();
            stmt.close();
//	    conn.close();
            return mfl;

        } catch (SQLException ex) {
//	    try {  conn.close(); } catch( Exception e) {}
            ortus.api.DebugLog(ERROR,"SQLException: " + ex);
        }
        return null;
    }

    public boolean createDB(Connection conn) {
       String sql = "";
       int rc = 0;
       String sqlpath = ortus.api.GetProperty("ortus/basepath","") + java.io.File.separator + "sql" + java.io.File.separator;
       ortus.api.DebugLog(INFO, "ortusDB: building tables");
       try {
         rc = executeSQLQueryFile(conn,sqlpath + "createschema.sql");
         rc = executeSQLQueryFile(conn,sqlpath + "createaliases.sql");
         rc = executeSQLQueryFile(conn,sqlpath + "createmedia.sql");
         rc = executeSQLQueryFile(conn,sqlpath + "createcast.sql");
         rc = executeSQLQueryFile(conn,sqlpath + "creategenre.sql");
         rc = executeSQLQueryFile(conn,sqlpath + "createmediavideos.sql");
         rc = executeSQLQueryFile(conn,sqlpath + "createtv.sql");
         rc = executeSQLQueryFile(conn,sqlpath + "createuser.sql");
         rc = executeSQLQueryFile(conn,sqlpath + "createviews.sql");
         rc = executeSQLQueryFile(conn,sqlpath + "createscrapperlog.sql");
         rc = executeSQLQueryFile(conn,sqlpath + "createfanart.sql");
         rc = executeSQLQueryFile(conn,sqlpath + "createcache.sql");
         rc = executeSQLQueryFile(conn,sqlpath + "createsyslog.sql");

       } catch ( Exception e2) {
         ortus.api.DebugLog(ERROR, "SQL: " + sql);
         ortus.api.DebugLog(ERROR, "ortusDB: create tables : Excetpion: " + e2);
         return false;
       }

       ortus.api.DebugLog(INFO, "ortusDB: create table successful");
       return true;
    }
}
